package com.experience.yledu.insert;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class insertSQL {

		public static FileReader fr;
		public static List<String> sqlList = new ArrayList<String>();
		public static Connection conn;

		public static void getList() throws Exception {
			try {
				fr = new FileReader("E:/words.sql");
				BufferedReader br = new BufferedReader(fr);
				String line = "";
				while ((line = br.readLine()) != null) {
					sqlList.add(line);
				}
				br.close();
				fr.close();
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		public static void connect() throws Exception {
			String driver = "com.mysql.jdbc.Driver";
			String dbName = "qsdb";
			String passwrod = "root";
			String userName = "root";
			String url = "jdbc:mysql://116.62.244.135:3306/" + dbName;
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, passwrod);
		}

		public static void main(String[] args) throws Exception {
			//Long begin = new Date().getTime();
			getList();
			connect();
			// PreparedStatement ps = conn.prepareStatement("select * from
			// qd_e_subject");
			// ResultSet rs = ps.executeQuery();
			// while (rs.next()) {
			// System.out.println("id : " + rs.getString(1) );
			// }
			
			
			Runnables run = new Runnables(conn, sqlList);
			Thread thread = new Thread(run, "线程1");
			Thread thread2 = new Thread(run, "线程2");
			Thread thread3 = new Thread(run, "线程3");
			Thread thread4 = new Thread(run, "线程4");
			Thread thread5 = new Thread(run, "线程5");
			Thread thread6 = new Thread(run, "线程6");
			Thread thread7 = new Thread(run, "线程7");
			Thread thread8 = new Thread(run, "线程8");
			Thread thread9 = new Thread(run, "线程9");
			Thread thread10 = new Thread(run, "线程10");
			thread.start();
			thread2.start();
			thread3.start();
			thread4.start();
			thread5.start();
			thread6.start();
			thread7.start();
			thread8.start();
			thread9.start();
			thread10.start();
			Long end = new Date().getTime();
			// System.out.println("13万条数据插入花费时间 : " + (end - begin) / 1000 + " s");
		}
	}

	class Runnables implements Runnable {
		public volatile List<String> sqlList;
		public Connection conn;
		public boolean flag = true;
		public PreparedStatement pstmt;

		public Runnables(Connection conn, List<String> sqlList) {
			this.sqlList = sqlList;
			this.conn = conn;
		}

		@Override
		public void run() {
			while (flag) {
				synchronized (this) {
//					StringBuffer sb = new StringBuffer();
//					try {
//						conn.setAutoCommit(false);
//						PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");// 准备执行语句
//						for(int i=0;i<(sqlList.size()>=1000?1000:sqlList.size());i++){
//							//sb.append(sqlList.get(i).replace(";", ""));
//							pst.addBatch(sqlList.get(i).replace(";", ""));
//							//sb = new StringBuffer();
//						}
//						System.out.println("开始插入1000条数据");
//						//pst.addBatch(sb.toString().substring(0, sb.toString().length() - 1));
//						Long begin = new Date().getTime();
//						pst.executeBatch();
//						conn.commit();
//						Long end = new Date().getTime();
//						System.out.println("插入1000条数据完成");
//						System.out.println("1000条数据插入花费时间 : " + (end - begin) / 1000 + " s");
//						for(int i=0;i<1000;i++){
//							if(sqlList.size() == 0){
//								flag = false;
//								break;
//							}else{
//								sqlList.remove(0);
//							}
//						}
//						System.out.println("成功删除这1000条数据");
//					} catch (Exception e) {
//						e.getMessage();
//						System.out.println(e.getMessage());
//					}
					if (sqlList.size() >= 0) {
						try {
							pstmt = (PreparedStatement) conn.prepareStatement(sqlList.get(0));
							pstmt.executeUpdate();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						System.out.println(Thread.currentThread().getName() + "删除了一个还剩下" + (sqlList.size() - 1));
						System.out.println("删除的sql为" + sqlList.get(0));
						sqlList.remove(0);
					} else {
						flag = false;
						try {
							pstmt.close();
							conn.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				}
			}
		}

	}

